﻿using System;
using System.Data.Common;
using Dapper;
using MySql.Data.MySqlClient;
using TalksLog.Abstraction.Repositories;
using TalksLog.Entities;

namespace TalksLog.DapperRepository
{
    public class AccountRepository : AccountRepositoryBase
    {
        private string _connectionString;

        public AccountRepository(string connectionString)
        {
            _connectionString = connectionString;
        }

        public override int Add(Account account)
        {
            string sql = @"insert into accounts(Name,Email,Password,Salt,Gender,CreateTime)
                           values
                          (@Name,@Email,@Password,@Salt,@Gender,@CreateTime)";

            string getIdSql = "select last_insert_id()";

            using (DbConnection con = new MySqlConnection(_connectionString))
            {
                con.Execute(sql, param: account);
                return con.ExecuteScalar<int>(getIdSql);
            }
        }

        public override void Delete(int id)
        {
            string sql = $"delete from accounts where id = {id}";
            using (DbConnection con = new MySqlConnection(_connectionString))
            {
                con.Execute(sql);
            }
        }

        public override bool ExistsSameEmail(Account account)
        {
            string sql = null;          

            if (account.Id != null)
            {
                sql = $"SELECT IFNULL((SELECT 1 from accounts where id <> {account.Id} and Email = @email LIMIT 1), 0)";
            }
            else
            {
                sql = $"SELECT IFNULL((SELECT 1 from accounts where Email = @email LIMIT 1), 0)";
            }

            using(DbConnection con = new MySqlConnection(_connectionString))
            {
                int result = con.ExecuteScalar<int>(sql, param: new { Email = account.Email });
                return result == 1;
            }
        }

        public override bool ExistsSameName(Account account)
        {
            string sql = null;

            if (account.Id != null)
            {
                sql = $"SELECT IFNULL((SELECT 1 from accounts where id <> {account.Id} and `Name` = @Name LIMIT 1), 0)";
            }
            else
            {
                sql = $"SELECT IFNULL((SELECT 1 from accounts where `Name` = @Name LIMIT 1), 0)";
            }

            using (DbConnection con = new MySqlConnection(_connectionString))
            {
                int result = con.ExecuteScalar<int>(sql, param: new { Name = account.Name });
                return result == 1;
            }
        }

        public override bool ExistsSameName(int id, string name)
        {
            string sql = $"SELECT IFNULL((SELECT 1 from accounts where id <> {id} and `Name` = @Name LIMIT 1), 0)";
            using (DbConnection con = new MySqlConnection(_connectionString))
            {
                int result = con.ExecuteScalar<int>(sql, param: new { Name = name });
                return result == 1;
            }
        }

        public override Account Find(int id)
        {
            string sql = $"select Id, `Name`, Email, Gender,Salt,`Password`,d CreateTime from accounts where id = {id}";
            using (DbConnection con = new MySqlConnection(_connectionString))
            {
                var account = con.QueryFirstOrDefault<Account>(sql);
                return account;
            }
        }

        public override Account Find(string email, string password)
        {
            string sql = "select Id, `Name`, Email, Gender, CreateTime from accounts where email = @Email and password = Md5(CONCAT(@Password,Salt))";
            using (DbConnection con = new MySqlConnection(_connectionString))
            {
                var account = con.QueryFirstOrDefault<Account>(sql, param: new { Email = email, Password = password});
                return account;
            }
        }

        public override void UpdateName(int id, string newName)
        {
            string sql = $"update accounts set name = @Name where id = {id}";
            using (DbConnection con = new MySqlConnection(_connectionString))
            {
                con.Execute(sql, param: new { Name = newName });
            }
        }

        public override void UpdatePassword(int id, string newPassword)
        {
            string sql = $"update accounts set password = Md5(CONCAT(@NewPassword, Salt)) where id = {id}";
            using (DbConnection con = new MySqlConnection(_connectionString))
            {
                con.Execute(sql, param: new { NewPassword = newPassword });
            }
        }
    }
}
